﻿if exists (select * from dbo.sysobjects where id = 
object_id(N'[dbo].[sp_SalesByCategory]') 
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_SalesByCategory]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create PROCEDURE sp_SalesByCategory
   @categoryId nvarchar(20) = null	
AS
BEGIN
	SET NOCOUNT ON;
	SELECT 
	  Product.CategoryID, 
	  Category.Name as CategoryName, 
	  Product.Name as ProductName, 
	  Sum([OrderDetailsExtended].ExtendedPrice) AS ProductSales
	FROM Category, 
		 Product,
		 [Order],
		 (select * from udf_OrderDetailsExtended()) as [OrderDetailsExtended] 
	where [Order].ID = [OrderDetailsExtended].OrderID and
		   Product.ID = [OrderDetailsExtended].ProductID and
		   Category.ID = Product.CategoryID and
           (@categoryId is null or Category.ID = @categoryId)
	GROUP BY Product.CategoryID, 
			 Category.Name, 
			 Product.Name
	ORDER BY Category.Name

END
GO